﻿<# requires -version 2
.SYNOPSIS

.DESCRIPTION
	Backup SQL Server databases, database, differential or transaction log
.NOTES
Filename   : SqlAdminBackup.ps1
Author     : Niels Grove-Rasmussen
.LINK
.SYNTAX
	.\SqlAdminBackup.ps1 -ComputerName <Computername> -DbInstanceName <Database instance name>
.EXAMPLE
	.\SqlAdminBackup.ps1
#>
param ( [string]$ConfigFileName = 'SqlAdminBackup.config.ps1' )

Set-StrictMode -Version 2.0
Import-Module 'SQLPS' -DisableNameChecking

$DebugPreference = "Continue"
Write-Verbose "Configuration File Name = '$ConfigFileName'."

$ScriptStartTime = [System.DateTime]::Now

# SQL Server Root Folder path
$sql = @"
DECLARE @root_folder nvarchar(128);
EXECUTE [master].[dbo].[xp_instance_regread]
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key = N'SOFTWARE\Microsoft\MSSQLSERVER\Setup',
  @value_name = N'SQLDataRoot',
  @value = @root_folder OUTPUT;
SELECT @root_folder AS [root_folder];
"@
$SqlRootFolder = $(Invoke-SqlCmd -Query $sql).root_folder
Write-Verbose "SQL Server Root Folder = '$SqlRootFolder'."

# Read configuration file
$_ConfigFileFullName = "$SqlRootFolder\JOBS\$ConfigFileName"
Write-Debug "Configuration File Full Name = '$_ConfigFileFullName'."
try { . "$_ConfigFileFullName" }
catch { throw "Could not read configuration file '$ConfigFileName' in the folder '$SqlRootFolder\JOBS\'. Check script setup." }

# Initialize Log file
Write-Verbose "Task Name = '$TaskName'."
$LogFileName = "$SqlRootFolder\Log\$TaskName.{0:yyyyddMMTHHmmss}.log" -f $ScriptStartTime
Write-Verbose "Log file name = '$LogFileName'."
"{0:s}  Script '$($MyInvocation.InvocationName)' started with the configuration file '$ConfigFileName' by the user '$([Security.Principal.WindowsIdentity]::GetCurrent().Name)'." -f $ScriptStartTime > $LogFileName
New-Object System.String('-', 42) >> $LogFileName


# Check and log database instance parameters from configuration file
"Computer Name      = '$ComputerName'" >> $LogFileName
# ToDo: Check if script is running against local host - MUST
#if($ComputerName -ne '(local)')

if ( $DbInstanceName -eq $null -or $DbInstanceName -eq '' -or $DbInstanceName -eq '.' )
{ "Database Instance  = (default)" >> $LogFileName }
else
{ "Database Instance  = '$DbInstanceName'" >> $LogFileName }


# Initialize connection to database server
if ( $DbInstanceName -eq $null -or $DbInstanceName -eq '' )
{ $ServerName = $ComputerName }
else 
{ $ServerName = "$ComputerName\$DbInstanceName" }
Write-Verbose "  Server Name = $ServerName"

$cnnStr = "Data Source=$ServerName;Integrated Security=SSPI;Application Name=SqlAdminBackup"
$Cnn = New-Object System.Data.SqlClient.SqlConnection $cnnStr
$Cmd = New-Object System.Data.SqlClient.SqlCommand
$Cmd.Connection = $Cnn

# SQL Server info
$Cmd.CommandText = @"
SELECT
CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX))) - 1) AS INT) AS [version_major],
SERVERPROPERTY('ProductLevel') AS [level],
SERVERPROPERTY('Edition') AS [edition];
"@
$Cnn.Open()
$Rdr = $Cmd.ExecuteReader([System.Data.CommandBehavior]::SingleRow)
$Rdr.Read() | Out-Null
$SqlVersionMajor = $Rdr.GetValue($Rdr.GetOrdinal("version_major"))  # SQL Server major version number
$SqlLevel = $Rdr.GetValue($Rdr.GetOrdinal("level"))  # SQL Server product level
$SqlEdition = $Rdr.GetValue($Rdr.GetOrdinal("edition"))  # SQL Server edition
$Rdr.Close()
$Cnn.Close()
"  Version: $SqlVersionMajor; Edition: '$SqlEdition'; Level: '$SqlLevel'" >> $LogFileName 

### Check and log parameters from configuration file
if ( $BackupFolder -eq $null -or $BackupFolder -eq '' ) {  # Get default Backup Path
	$Cmd.CommandText = '[master].[sys].[xp_instance_regread]'
	$Cmd.CommandType = [System.Data.CommandType]::StoredProcedure
	$Cmd.Parameters.Add("@rootkey", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
	$Cmd.Parameters['@rootkey'].Direction = [System.Data.ParameterDirection]::Input
	$Cmd.Parameters['@rootkey'].Value = 'HKEY_LOCAL_MACHINE'
	$Cmd.Parameters.Add("@key", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
	$Cmd.Parameters['@key'].Direction = [System.Data.ParameterDirection]::Input
	$Cmd.Parameters['@key'].Value = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLSERVER'
	$Cmd.Parameters.Add("@value_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
	$Cmd.Parameters['@value_name'].Direction = [System.Data.ParameterDirection]::Input
	$Cmd.Parameters['@value_name'].Value = 'BackupDirectory'
	$Cmd.Parameters.Add("@value", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
	$Cmd.Parameters['@value'].Direction = [System.Data.ParameterDirection]::Output
	$Cnn.Open()
	$_RowCount = $Cmd.ExecuteNonQuery()
	$Cnn.Close()
	$BackupFolder = $Cmd.Parameters['@value'].Value
	$Cmd.Parameters.Clear()  # Removed Parameter objects for reuse of Command object
	$Cmd.CommandType = [System.Data.CommandType]::Text  # Reset CommandType for reuse of Command object
	"Backup Folder      = '$BackupFolder' (default)" >> $LogFileName
}
else {
	if (Test-Path $BackupFolder) { 	"Backup Folder      = '$BackupFolder'" >> $LogFileName }
	else { 
		"{0:s}  The backup folder '$BackupFolder' does not exist. Check folder name or create folder."
		throw "The backup folder '$BackupFolder' does not exist.`nCheck logfile '$LogFileName' for details."
	}
}

if ( $BackupType -cne 'FULL' -and $BackupType -cne 'DIFF' -and $BackupType -cne 'LOG' ) {
	"{0:s}  The backup type '$BackupType' is not valid. Use 'FULL', 'DIFF' or 'LOG'." -f $([System.DateTime]::Now) >> $LogFileName
	throw "Error in parameter BackupType.`nCheck logfile '$LogFileName' for details."
}
"Backup Type        = '$BackupType'" >> $LogFileName

if ( $AddFull.GetType().Name -ne 'Boolean' ) {
	"{0:s}  The value '$AddFull' for the parameter AddFull is not valid. Use Boolean value." -f $([System.DateTime]::Now) >> $LogFileName
	throw "Error in parameter AddFull.`nCheck logfile '$LogFileName' for details."
}
"Add Full           = $AddFull" >> $LogFileName

if ( $BlockSize -lt 0 -and $BlockSize -ne $null ) {
	"{0:s}  The value '$BlockSize' is not valid." -f $([System.DateTime]::Now) >> $LogFileName
	throw "Error in parameter BlockSize.`nCheck logfile '$LogFileName' for details."
}
if ($BlockSize -eq $null) { "Block Size         = (default)" >> $LogFileName }
else { "Block Size         = $BlockSize Bytes" >> $LogFileName }

if ($BufferCount -lt 0 -and $BufferCount -ne $null) {
	"{0:s}  The value '$BufferCount' is not valid for the parameter BufferCount." -f $([System.DateTime]::Now) >> $LogFileName
	throw "Error in parameter BufferCount.`nCheck logfile '$LogFileName' for details."
}
if ($BufferCount -eq $null) { "Buffer Count       = (default)" >> $LogFileName }
else { "Buffer Count       = $BufferCount" >> $LogFileName }

"Do CheckSum        = $DoChecksum" >> $LogFileName

if ( $DoCompression -ne $null -and $DoCompression.GetType().Name -ne 'Boolean' ) {
	"{0:s}  The value '$DoCompression' is not valid for the parameter DoCompression." -f $([System.DateTime]::Now) >> $LogFileName
	throw "Error in parameter DoCompression.`nCheck logfile '$LogFileName' for details."
}
if ( $DoCompression -eq $null ) { "Do Compression     = (default)" >> $LogFileName }
else { "Do Compression    = $DoCompression" >> $LogFileName }

"Copy Only          = $CopyOnly" >> $LogFileName

"Backup Description = '$BackupDescription'" >> $LogFileName

"Do Execute         = $DoExecute" >> $LogFileName

if ($ExpireDate -eq $null) { "Expire Date        = (NULL)" >> $LogFileName }
else { "Expire Date        = $ExpireDate" >> $LogFileName }

if ($File -eq $null) { "File               = (NULL)" >> $LogFileName }
else { "File                   = $File" >> $LogFileName }

if ($FileCount -eq $null) { "File Count         = (NULL)" >> $LogFileName }
else { "File Count         = $FileCount" >> $LogFileName }

if ($FileGroup -eq $null) { "File Group         = (NULL)" >> $LogFileName }
else { "File Group         = $FileGroup" >> $LogFileName }

if ($MaxTransferSize -eq $null) { "Max Transfer Size  = (default)" >> $LogFileName }
else { "Max Transfer Size  = $MaxTransferSize Bytes" >> $LogFileName }

if ($Name -eq $null) { "Backup Name        = (NULL)" >> $LogFileName }
else { "Backup Name        = $Name" >> $LogFileName }

if ($RetainDays -eq $null) { "Retain days        = (NULL)" >> $LogFileName }
else { "Retain days        = $RetainDays" >> $LogFileName }

"Verify backup      = $DoVerify" >> $LogFileName

New-Object System.String('=',42) >> $LogFileName


### Select databases
$DbList = @()
foreach ($DbName in $DbNames) {
	switch ($DbName) {
		'__ALL_DATABASES' {
			$Cmd.CommandText = "SELECT [name] FROM [master].[sys].[databases];"
			break
		}
		'__SYSTEM_DATABASES' {
			$Cmd.CommandText = "SELECT [name] FROM [master].[sys].[databases] WHERE [name] IN ('master','model','msdb','tempdb');"
			break
		}
		'__USER_DATABASES' {
			$Cmd.CommandText = "SELECT [name] FROM [master].[sys].[databases] WHERE [name] NOT IN ('master','model','msdb','tempdb')"
			break
		}
		default {
			$Cmd.CommandText = "SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'$DbName';"
			break
		}
	}
	# ToDo: Add replication database
	
	$Cnn.Open()
	$Rdr = $Cmd.ExecuteReader()
	while ( $Rdr.Read() ) {
		$_DbName = $Rdr.GetValue($Rdr.GetOrdinal("name"))
		$DbList += $_DbName
	}
	$Rdr.Close()
	$Cnn.Close()
}
# ToDo: Check for dublicate database names.
"{0:s}  Selected databases:" -f $([System.DateTime]::Now) >> $LogFileName
foreach ($DbName in $DbList) { "  [$DbName]" >> $LogFileName }


### Backup
foreach ($DbName in $DbList) {
	Write-Verbose "[$DbName]..."

	##Create backup file name
	if ($BackupType -eq 'LOG') { $_BackupFileType = 'trn' }
	else { $_BackupFileType = 'bak' }
	$_BackupFileDatetime = "{0:yyyy'-'MM'-'dd'T'HHmmss'_'fffffff}" -f $([System.DateTime]::Now)
	$BackupFileName = "$BackupFolder\$DbName`_$BackupType`_$_BackupFileDatetime.$_BackupFileType"
	Write-Verbose "  Backup file name = '$BackupFileName'"
	
	## Create T-SQL statements
	if ($BackupType -eq 'LOG') { $sqlBackup = "BACKUP LOG" }
	else { $sqlBackup = "BACKUP DATABASE" }
	$sqlBackup += " [$DbName] TO DISK = N'$BackupFileName'"
	$sqlBackup += ' WITH'
	if ($BackupType -eq 'DIFF') { $sqlBackup += " DIFFERENTIAL," }
	$sqlBackup += ' STATS = 5'
	$sqlBackup += ';'  # End of T-SQL backup statement
	"{0:s}  Backup T-SQL Statement:`n$sqlBackup" -f $([System.DateTime]::Now) >> $LogFileName
	
	$sqlVerify = "RESTORE VERIFYONLY FROM DISK = N'$BackupFileName' WITH STATS = 5;"
	Write-Verbose "  $sqlVerify"

	## Execute backup
	$Cmd.CommandText = $sqlBackup
	$BackupStopwatch = [System.Diagnostics.Stopwatch]::StartNew()
	# Do...
	$BackupStopwatch.Stop()
	"{0:s}  Backup time = $($BackupStopwatch.Elapsed.ToString()) [HH:mm:ss.ddd]." -f $([System.DateTime]::Now) >> $LogFileName

	## Verify Backup
	$Cmd.CommandText = $sqlVerify

	## DatabaseOwner
	$Cmd.CommandText = @"
SELECT 
	[server_principals].[name] AS [owner_name]
FROM [master].[sys].[databases]
INNER JOIN [master].[sys].[server_principals]
ON [databases].[owner_sid] = [server_principals].[sid]
WHERE [databases].[name] = N'$DbName';
"@
	$Cnn.Open()
	$Rdr = $Cmd.ExecuteReader([System.Data.CommandBehavior]::SingleRow)
	$Rdr.Read() | Out-Null
	$DbOwnerName = $Rdr.GetValue($Rdr.GetOrdinal("owner_name"))  # Database owner name
	$Rdr.Close()
	$Cnn.Close()
	
	# Filenames in full backup chain
	if ($BackupType -eq 'FULL') {
		$BackupFileName_Full = $BackupFileName
		$BackupFileName_Diff = $null
		$BackupFileNames_Log = @()  # array of log backup filenames
	}
	else {  # Last full backup file
		$BackupFileName_Full = '<NOT VALID>'  # ToDo
	}
	if ($BackupType -eq 'DIFF') {
		$BackupFileName_Full = '<NOT VALID>'  # ToDo
		$BackupFileName_Diff = $BackupFileName
		$BackupFileNames_Log = @()  # array of log backup filenames
	}
	else {  # Last differential backup file
		$BackupFileName_Diff = '<NOT_VALID>'  # ToDo
	}
	if ($BackupType -eq 'LOG') {
		$BackupFileName_Full = '<NOT VALID>'  # ToDo
		$BackupFileName_Diff = '<NOT VALID>'  # ToDo
		$BackupFileNames_Log = @()
		$BackupFileNames_Log += $BackupFileName
	}
	else {
		$BackupFileNames_Log = @()
	}

	## Generate Restore script
	$RestoreScriptFileName = "$BackupFolder\$DbName`_RESTORE`_$_BackupFileDatetime.sql"
	"{0:s}  Restore script = '$RestoreScriptFileName'" >> $LogFileName
	"-- Description : Restore the database [$DbName]." > $RestoreScriptFileName
	"-- Created     : {0:s} by the script $($MyInvocation.InvocationName)." -f $([System.DateTime]::Now) >> $RestoreScriptFileName
	"-- Author     : $([Security.Principal.WindowsIdentity]::GetCurrent().Name)" >> $RestoreScriptFileName
	"-- Example    : sqlcmd.exe ... (TBD)" >> $RestoreScriptFileName
	# ToDo : Restore using backup NIC (<hostname>.backup.dom) - check if NIC exists
	"`nRESTORE DATABASE [" + $DbName + "]" >> $RestoreScriptFileName
	"FROM DISK = N'$BackupFileName'" >> $RestoreScriptFileName
	"WITH RECOVERY;" >> $RestoreScriptFileName
	"PRINT N'>> Restore full backup from {0:s}.';`nGO" -f $_BackupFileDateTime >> $RestoreScriptFileName
	"EXECUTE AS LOGIN='$DbOwnerName';`nGO" >> $RestoreScriptFileName
	# ToDo : MOVE files to default locations
	"RESTORE DATABASE [$DbName]`nFROM DISK = N'$BackupFileName_Full'`nWITH STATS = 5, NORECOVERY;`nGO" >> $RestoreScriptFileName
	if ($BackupFileName_Diff -ne $null) {  # A differential backup file exists
		#"`nPRINT '>> Restore differential backup from {o:s}.';`nGO" -f $BackupFileTime_Diff  # ToDo: Handle file time
		"`nPRINT '>> Restore differential backup from {o:s}.';`nGO" >> $RestoreScriptFileName
		"RESTORE DATABASE [$DbName]`nFROM DISK = N'$BackupFileName_Diff'`nWITH STATS = 5, NORECOVERY;`nGO" >> $RestoreScriptFileName
	}
	else {
		"`nPRINT '>> No differential backup to restore.';`nGO" >> $RestoreScriptFileName
	}
	if ($BackupFileNames_Log.length -gt 0) {
		"`nPRINT '>> Restore log backup(-s).';`nGO" >> $RestoreScriptFileName
		foreach ($BackupFileName in $BackupFileName_Log) {
			#"PRINT '>> Restore log backup from {0:s}.';`nGO" -f $_.LastWriteTime.ToUniversalTime()  ToDo: Handle file time
			"RESTORE LOG [$DbName)]`nFROM DISK = N'$BackupFileName'`nWITH STATS, NORECOVERY;`nGO" >> $RestoreScriptFileName
		}
	}
	else {
		"`nPRINT '>> No transactionlog backups to restore.';`nGO" >> $RestoreScriptFileName
	}
}


# Description : Get newest full database backup file
function Get-Backupfile {
param(
	$Backup_Path = $(throw "Path for the backp files is needed as parameter."),
	$BackupType = $(throw "Type of the backup is needed as parameter { FULL | DIFF }.")
)
	# Get newest backup file in folder
	if (($BackupType -ceq 'FULL') -or ($BackupType -ceq 'DIFF'))
	{ $BackupFile = Get-ChildItem "$($Backup_Path)\$($BackupType)" -Filter '*.bak' | Sort-Object LastWriteTime | Select-Object -Last 1 }
	else { throw "The Backuptype '$($BackupType)' is unknown. Please use 'FULL' or 'DIFF'." }
	
	# Check if a backup file is found for Full backup
	if($BackupFile -eq $null -and $BackupType -ceq 'FULL') { throw "No backup file (*.bak) found in the folder '$($Backup_Path)\$($BackupType)'." }

	$BackupFile
}


#*** INVOKE ***

#Set-Databases
